<?php
/**
 *
 * @author bedirhan
 */
class MSALServiceDB {
        private static $server = "127.0.0.1";
        private static $dbname = "msaldb";
        private static $dbuser = "msaluser";
        private static $dbpass = "msalpass";

        private static $db_alerts_table = "alerts";
        private static $db_auditlog_table = "audit_log";

        private static $setSize = 8;

        private static function getConnection() {

            $mysqli = new mysqli(self::$server, self::$dbuser, self::$dbpass, self::$dbname);

            if (mysqli_connect_errno()) {
                throw new MSALDBException("Cannot connect to database");
            }

            return $mysqli;
        }

        /*
         * $targetDate is obvious
         * $setIndex is the index of the msal set that belong the given
         *  $targetDate. self::$setSize defines the size of the set returned
         */
        public static function getMSALRows($targetDate, $setIndex){

            // connect
            $conn =  self::getConnection();

            // SourceIP, AuditLogTime from $db_auditlog_table
            // Severity, Msg, RuleID from $db_alerts_table
            // LIMIT X,Y: X is the starting row index starting from 0, Y is the row size to be returned
            $rowindex = ($setIndex * self::$setSize);

            // LIMITATION:
            // Cannot bind parameter to limit offset!!!
            // http://bugs.php.net/47960
            $stmt = $conn->prepare("select AuditLogID, SourceIP, AuditLogTime, Severity, Msg, RuleID
                            from audit_log as adt, alerts as alt 
                            where adt.AuditLogUniqueID = alt.AuditLogUniqueID and adt.AuditLogDate = ? 
                            order by Severity limit ?, " . self::$setSize);

            $stmt->bind_param('si', $targetDate, $rowindex);

            $stmt->execute();
            
            $row = array();
            self::stmt_bind_assoc($stmt, $row);

            // loop through all result rows
            $rows = array();
            while ($stmt->fetch()) {
                $rows[count($rows)] = $row;
            }
            
            $stmt->close();

            $setIndex = $setIndex + self::$setSize;

            return $rows;
        }

        // http://www.php.net/manual/en/mysqli-stmt.fetch.php#82742
        private static function stmt_bind_assoc (&$stmt, &$out) {
            $data = mysqli_stmt_result_metadata($stmt);
            $fields = array();
            $out = array();

            $fields[0] = $stmt;
            $count = 1;

            while($field = mysqli_fetch_field($data)) {
                $fields[$count] = &$out[$field->name];
                $count++;
            }
            call_user_func_array(mysqli_stmt_bind_result, $fields);
        }
}
?>
